package com.iwhalecloud.dict.service;

import com.iwhalecloud.dict.dao.PostgreSqlDao;
import com.iwhalecloud.dict.util.OperExcelSheet;
import com.iwhalecloud.dict.bean.ExcelInfoBeResult;
import com.iwhalecloud.dict.bean.ExcelInfor;
import com.iwhalecloud.dict.dao.PostgreSqlDao;
import com.iwhalecloud.dict.util.OperExcelSheet;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.List;

public class BenefitEvaluation {
    public void operDataFromExcel2DB(long oppId,String filePath,int operType,int acctMonth){
        OperExcelSheet operExcelSheet= new OperExcelSheet();
        PostgreSqlDao dao = new PostgreSqlDao();
        try (InputStream inp = new FileInputStream(filePath)) {
            XSSFWorkbook wb = new XSSFWorkbook(inp);
            XSSFSheet sheet1 = wb.getSheet("经济效益评估");
            int rowNum= sheet1.getLastRowNum();
            // 处理明细数据,因为不连续，所以需要指定起始位置，分段处理
            List<ExcelInfor> list=operExcelSheet.readDetailDataFromSheet2List(sheet1,3,48,1);
            list.addAll(operExcelSheet.readDetailDataFromSheet2List(sheet1,50,63,2));
            list.addAll(operExcelSheet.readDetailDataFromSheet2List(sheet1,70,88,3));
            // 插入结果表
            dao.insertBeDetail(list,oppId,operType);
            // 处理最终效益数据
            List<ExcelInfoBeResult> list2 = operExcelSheet.readResultDataFromSheet2List(sheet1,63,66,4);
            list2.addAll(operExcelSheet.readResultDataFromSheet2List(sheet1,88,91,5));
            // 插入结果表
            dao.insertBeResult(list2,oppId,operType);

            wb.close();

            /**
             *  这个地方需要数据处理后再插入结果表，带商机ID、操作类型（前评估、签约评估、过程评估、后评估）
             *  调用存储过程
             *  存储过程是所有评估所共用，EXCEL表是放到中间表中，通过存储过程把商机评估结果转存结果表。
             */
            int out=dao.callProcedure("proc_gen_benefit_eval_result",oppId,operType,acctMonth);

            if(out==0){
                System.out.println("执行成功！");
            }else {
                System.out.println("执行失败！");
            }

        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public void operDataFromDb2Excel(long oppId,int operType,int acctMonth){
        // operType 1、售前评估  2、签约评估  3、过程评估（过程评估的时候需要判断是否为最后一个月评估，如果是，则需要添加一个方法给到存储数据  4、后评估）
        // 模板Excel在固定位置
        // 判断一下 operType是否在合理范围内

        OperExcelSheet operExcelSheet= new OperExcelSheet();
        PostgreSqlDao dao = new PostgreSqlDao();
        // 模板存放地址，到时候会调整
        String souceFile="F:\\project\\idea2018.3\\springbootdemo\\target\\classes\\model.xlsx";
        String targetFile=souceFile.replace("model.xlsx","OPP"+oppId+"_"+operType+".xlsx");
        try {
            // 先删除目标文件，这个文件不保存
            File file = new File(targetFile);
            if(file.exists()){
                file.delete();
            }
            // 从模板拷贝文件
            operExcelSheet.copyFileUsingJava7Files(new File(souceFile),new File(targetFile));
        }catch (IOException e){
            e.printStackTrace();
        }
        // 从数据库读取数据
        // 带数据到Excel中进行更新
        try (InputStream inp = new FileInputStream(targetFile)) {
            XSSFWorkbook wb = new XSSFWorkbook(inp);
            XSSFSheet sheet1 = wb.getSheet("经济效益评估");
            //int rowNum = sheet1.getLastRowNum();

            // 业务处理
            // operType= 1、售前评估  2、签约评估  3、过程评估（过程评估的时候需要判断是否为最后一个月评估，如果是，则需要添加一个方法给到存储数据  4、后评估）
            //           签约评估需要做三次： operType： 1、售前评估 20、签约评估   21、签约评估中的IT科目评估   22、签约评估中的CT科目评估 3、过程评估 4、后评估
            if(operType==1){
                // 只取哪些行的数据，售前从填报表里面取数据，根据行号进行模板表数据更新，后取结果表数据到数据库
                // 只取要更新的数据
                String fillDataSql="select a.year_1,a.year_2 , a.year_3 , a.year_4 , a.year_5 , a.year_6 , a.year_7 , a.year_8 , a.year_9 , a.year_10 , a.opp_id, a.index_id " +
                    " from fill_benefit_evaluation a " +
                    "inner join attr_evaluation_indicator b on a.index_id=b.attr_id " +
                    "where a.opp_id="+oppId+" " +    // 取更新的商机
                    "  and b.remark='1000' " +
                    "  and a.indicate_code!='126' " +
                    "  and a.fill_type='QI' ";           // 取更新的index
                sheet1=operExcelSheet.updateSheetFromList(dao.queryDataFromDB(fillDataSql), sheet1,4);
                String other="select a.year_1,a.year_2 , a.year_3 , a.year_4 , a.year_5 , a.year_6 , a.year_7 , a.year_8 , a.year_9 , a.year_10 , a.opp_id, a.index_id " +
                    " from fill_benefit_evaluation a " +
                    "inner join attr_evaluation_indicator b on a.index_id=b.attr_id " +
                    "where a.opp_id="+oppId+" " +    // 取更新的商机
                    "  and b.remark='1000' " +
                    "  and (a.indicate_code='126' or index_id in(16,24,28,33,35,36,53,77) ) " +
                    "  and a.fill_type='QI' ";           // 取更新的index
                sheet1=operExcelSheet.updateSheetFromList(dao.queryDataFromDB(other), sheet1,16);
            }else if(operType!=1) {
                // 签约评估 需要 从之前的数据里面取一些值，再从出帐计划里面取一些值，写入 excel 再把结果存下来
                // 1、根据商机ID和操作类型取数据,填报更新
                // 填报的含税金额
                String before_sql="select a.year1_amount,a.year2_amount , a.year3_amount , a.year4_amount , a.year5_amount , a.year6_amount , a.year7_amount , a.year8_amount , a.year9_amount , a.year10_amount , a.opp_id, a.index_id " +
                    " from mid_opp_benefit_evaluation a " +
                    "inner join attr_evaluation_indicator b on a.index_id=b.attr_id  " +
                    "where a.opp_id="+oppId+" " +
                    "  and b.remark='1000' " +
                    "  and a.oper_type=1 " +
                    "  and b.attr_major_type <> 'INCOME' ";
                sheet1=operExcelSheet.updateSheetFromList(dao.queryDataFromDB(before_sql), sheet1,4);
                // 填报的单价部分
                String before_price_sql="select a.year1_price,a.year2_price,a.year3_Price , a.year4_Price , a.year5_Price , a.year6_Price , a.year7_Price , a.year8_Price , a.year9_Price , a.year10_Price,a.opp_id,a.index_id " +
                    " from mid_opp_benefit_evaluation a " +
                    "inner join attr_evaluation_indicator b on a.index_id=b.attr_id  " +
                    "where a.opp_id="+oppId+" " +
                    "  and b.remark='1000' " +
                    "  and a.oper_type=1 " +
                    "  and b.attr_major_type <> 'INCOME' " +
                    "  and index_id in(16,24,28,33,35,36,53,77) ";
                sheet1=operExcelSheet.updateSheetFromList(dao.queryDataFromDB(before_price_sql), sheet1,16);

                if(operType==20){
                    // 2、收入取收入分帐计划表中的收入来更新EXCEL进行计算
                    String proj_income_from_plan="" +
                        "select sum(case when a.charge_year=b.min_charge_year then a.charge_amount_year end) as year1_amount ,    \n" +
                        "       sum(case when a.charge_year=b.min_charge_year+1 then a.charge_amount_year end) as year2_amount ,  \n" +
                        "       sum(case when a.charge_year=b.min_charge_year+2 then a.charge_amount_year end) as year3_amount ,  \n" +
                        "       sum(case when a.charge_year=b.min_charge_year+3 then a.charge_amount_year end) as year4_amount ,  \n" +
                        "       sum(case when a.charge_year=b.min_charge_year+4 then a.charge_amount_year end) as year5_amount ,  \n" +
                        "       sum(case when a.charge_year=b.min_charge_year+5 then a.charge_amount_year end) as year6_amount ,  \n" +
                        "       sum(case when a.charge_year=b.min_charge_year+6 then a.charge_amount_year end) as year7_amount ,  \n" +
                        "       sum(case when a.charge_year=b.min_charge_year+7 then a.charge_amount_year end) as year8_amount ,  \n" +
                        "       sum(case when a.charge_year=b.min_charge_year+8 then a.charge_amount_year end) as year9_amount ,  \n" +
                        "       sum(case when a.charge_year=b.min_charge_year+9 then a.charge_amount_year end) as year10_amount , \n" +
                        "       a.opp_id,    \n" +
                        "       a.index_id   \n" +
                        "  from (select c.attr_id index_id,a.opp_id , a.charge_year ,sum(a.charge_amount::decimal) charge_amount_year  \n" +
                        "          from prod_charge_plan a  \n" +
                        "          left join prod_expense_contrast b on a.prod_code =b.prod_code  \n" +
                        "          left join attr_evaluation_indicator c on b.income_code = c.attr_code  \n" +
                        "         where c.remark = '1000'  \n" +
                        "           and a.opp_id="+oppId+"  \n" +
                        "         group by c.attr_id ,a.opp_id,a.charge_year  \n" +
                        "           ) a\n" +
                        "      left join (\n" +
                        "        select opp_id,min(charge_year) min_charge_year from prod_charge_plan where opp_id='"+oppId+"' group by opp_id  \n" +
                        "        ) b on a.opp_id =b.opp_id  \n" +
                        "      group by a.index_id,a.opp_id  ";
                    sheet1=operExcelSheet.updateSheetFromList(dao.queryDataFromDB(proj_income_from_plan), sheet1,4);
                    System.out.println("ICT 签约效益评估");
                }
                if(operType==21){
                    // 取IT类科目
                    String proj_IT_income_from_plan="" +
                        "select sum(case when a.charge_year=b.min_charge_year then a.charge_amount_year end) as year1_amount ,    \n" +
                        "       sum(case when a.charge_year=b.min_charge_year+1 then a.charge_amount_year end) as year2_amount ,  \n" +
                        "       sum(case when a.charge_year=b.min_charge_year+2 then a.charge_amount_year end) as year3_amount ,  \n" +
                        "       sum(case when a.charge_year=b.min_charge_year+3 then a.charge_amount_year end) as year4_amount ,  \n" +
                        "       sum(case when a.charge_year=b.min_charge_year+4 then a.charge_amount_year end) as year5_amount ,  \n" +
                        "       sum(case when a.charge_year=b.min_charge_year+5 then a.charge_amount_year end) as year6_amount ,  \n" +
                        "       sum(case when a.charge_year=b.min_charge_year+6 then a.charge_amount_year end) as year7_amount ,  \n" +
                        "       sum(case when a.charge_year=b.min_charge_year+7 then a.charge_amount_year end) as year8_amount ,  \n" +
                        "       sum(case when a.charge_year=b.min_charge_year+8 then a.charge_amount_year end) as year9_amount ,  \n" +
                        "       sum(case when a.charge_year=b.min_charge_year+9 then a.charge_amount_year end) as year10_amount , \n" +
                        "       a.opp_id, \n" +
                        "       a.index_id \n" +
                        "  from (select c.attr_id index_id,a.opp_id , a.charge_year ,sum(a.charge_amount::decimal) charge_amount_year  \n" +
                        "          from prod_charge_plan a  \n" +
                        "          left join prod_expense_contrast b on a.prod_code =b.prod_code \n" +
                        "          left join attr_evaluation_indicator c on b.income_code = c.attr_code \n" +
                        "         where c.remark = '1000'  \n" +
                        "           and a.opp_id="+oppId+"  \n" +
                        "           and c.prod_type='IT' " +
                        "         group by c.attr_id ,a.opp_id,a.charge_year  \n" +
                        "           ) a\n" +
                        "      left join (\n" +
                        "         select opp_id,min(charge_year) min_charge_year from prod_charge_plan where opp_id="+oppId+" group by opp_id  \n" +
                        "        ) b on a.opp_id =b.opp_id  \n" +
                        "      group by a.index_id,a.opp_id  ";
                    sheet1=operExcelSheet.updateSheetFromList(dao.queryDataFromDB(proj_IT_income_from_plan), sheet1,4);
                    System.out.println("IT 签约效益评估");
                }
                if(operType==22){
                    // 取CT类科目
                    String proj_CT_income_from_plan="" +
                        "select sum(case when a.charge_year=b.min_charge_year then a.charge_amount_year end) as year1_amount ,  \n" +
                        "       sum(case when a.charge_year=b.min_charge_year+1 then a.charge_amount_year end) as year2_amount ,\n" +
                        "       sum(case when a.charge_year=b.min_charge_year+2 then a.charge_amount_year end) as year3_amount ,\n" +
                        "       sum(case when a.charge_year=b.min_charge_year+3 then a.charge_amount_year end) as year4_amount ,\n" +
                        "       sum(case when a.charge_year=b.min_charge_year+4 then a.charge_amount_year end) as year5_amount ,\n" +
                        "       sum(case when a.charge_year=b.min_charge_year+5 then a.charge_amount_year end) as year6_amount ,\n" +
                        "       sum(case when a.charge_year=b.min_charge_year+6 then a.charge_amount_year end) as year7_amount ,\n" +
                        "       sum(case when a.charge_year=b.min_charge_year+7 then a.charge_amount_year end) as year8_amount ,\n" +
                        "       sum(case when a.charge_year=b.min_charge_year+8 then a.charge_amount_year end) as year9_amount ,\n" +
                        "       sum(case when a.charge_year=b.min_charge_year+9 then a.charge_amount_year end) as year10_amount ,\n" +
                        "       a.opp_id, \n" +
                        "       a.index_id \n" +
                        "      from (select c.attr_id index_id,a.opp_id , a.charge_year ,sum(a.charge_amount::decimal) charge_amount_year  \n" +
                        "              from prod_charge_plan a  \n" +
                        "              left join prod_expense_contrast b on a.prod_code =b.prod_code \n" +
                        "              left join attr_evaluation_indicator c on b.income_code = c.attr_code \n" +
                        "             where c.remark = '1000'  \n" +
                        "               and a.opp_id="+oppId+"  \n" +
                        "               and c.prod_type='CT' " +
                        "             group by c.attr_id ,a.opp_id,a.charge_year  \n" +
                        "           ) a\n" +
                        "      left join (\n" +
                        "        select opp_id,min(charge_year) min_charge_year from prod_charge_plan where opp_id="+oppId+" group by opp_id  \n" +
                        "       ) b on a.opp_id =b.opp_id  \n" +
                        "      group by a.index_id,a.opp_id  ";
                    sheet1=operExcelSheet.updateSheetFromList(dao.queryDataFromDB(proj_CT_income_from_plan), sheet1,4);
                    System.out.println("CT 签约效益评估");

                }
                if(operType==30){
                    // 过程评估，需要从之前的数据里面取一些值，再从帐单表里面取一些值，写入excel 再把结果存下来
                    // 过程评估是按月发起，所以可以采用后台存储过程进行数据准备，准备完成后，按商机发起评估，因为计算过程使用到EXCEL模板，所以一次只能计算一个商机的效益评估。
                    // 结果表
                    String proj_CT_income_from_plan="select " +
                        "";
                    // 取值重新更新一下（因为成本部分)
                    // 这个逻辑比较不通用，后评估在后台脚本里面进行帐期判断后处理
                }
                if(operType==31){
                    // 过程评估，需要从之前的数据里面取一些值，再从帐单表里面取一些值，写入excel 再把结果存下来
                    // 过程评估是按月发起，所以可以采用后台存储过程进行数据准备，准备完成后，按商机发起评估，因为计算过程使用到EXCEL模板，所以一次只能计算一个商机的效益评估。
                    // 结果表
                    String proj_CT_income_from_plan="";
                    // 取值重新更新一下（因为成本部分)
                    // 这个逻辑比较不通用，后评估在后台脚本里面进行帐期判断后处理
                }
                if(operType==32){
                    // 过程评估，需要从之前的数据里面取一些值，再从帐单表里面取一些值，写入excel 再把结果存下来
                    // 过程评估是按月发起，所以可以采用后台存储过程进行数据准备，准备完成后，按商机发起评估，因为计算过程使用到EXCEL模板，所以一次只能计算一个商机的效益评估。
                    // 结果表
                    String proj_CT_income_from_plan="";
                    // 取值重新更新一下（因为成本部分)
                    // 这个逻辑比较不通用，后评估在后台脚本里面进行帐期判断后处理
                }
            }

            // 更新Excel
            // 强制刷新公式
            XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
            try (OutputStream fileOut = new FileOutputStream(targetFile)) {
                wb.write(fileOut);
            }
            wb.close();

            // 处理完后要取值入库
            operDataFromExcel2DB(oppId,targetFile,operType, acctMonth);

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
}
